﻿// ***********************************************************************
// <copyright file="DQiushibaike.cs" company="四川全球通">
// Copyright (c) 四川全球通. All rights reserved.</copyright>
// Assembly         : Tzq.DataService.DAL
// Author            : 谭志强
// Created          : 2016/1/13 10:21:53
// <summary></summary>
// ***********************************************************************

using Tzq.DataService.Model;
using Common.DBUtility;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Tzq.DataService.DAL
{
    public class DQiushibaike
    {

        /// <summary>
        /// 根据企业ID获取公众号信息
        /// </summary>
        /// <param name="trans"></param>
        /// <param name="conn"></param>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public List<MQiushibaike> GetJokeByKeyWords(IDbTransaction trans, IDbConnection conn, string keywords)
        {
            string sqlText = @"select KeyID,JokerName,JokeContent,Lauds,IsDelete  from Qiushibaike WHERE JokeContent like @JokeContent and IsDelete = 0 limit 3";
            MySqlParameters destionParameters = new MySqlParameters();
            destionParameters.Add(new MySqlParameter() { ParameterName = "@JokeContent", MySqlDbType = MySqlDbType.VarChar, Value = string.Format("%{0}%", keywords) });
            List<MQiushibaike> qiushibaikeList = new List<MQiushibaike>();
            MQiushibaike qiushibaike = null;
            using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sqlText, destionParameters.ToArray()))
            {
                while (reader.Read())
                {
                    qiushibaike = new MQiushibaike();
                    qiushibaike.KeyID = reader["KeyID"] == DBNull.Value ? string.Empty : reader["KeyID"].ToString();
                    qiushibaike.JokerName = reader["JokerName"] == DBNull.Value ? string.Empty : reader["JokerName"].ToString();
                    qiushibaike.JokeContent = reader["JokeContent"] == DBNull.Value ? string.Empty : reader["JokeContent"].ToString();
                    qiushibaike.Lauds = reader["Lauds"] == DBNull.Value ? 0 : Convert.ToInt32(reader["Lauds"]);

                    qiushibaikeList.Add(qiushibaike);
                }
            }

            return qiushibaikeList;
        }

        /// <summary>
        /// 根据企业ID获取公众号信息
        /// </summary>
        /// <param name="trans"></param>
        /// <param name="conn"></param>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public List<MQiushibaike> GetJokeByRandom(IDbTransaction trans, IDbConnection conn)
        {
            string sqlText = @"SELECT * 
FROM qiushibaike AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(KeyID) FROM qiushibaike)-(SELECT MIN(KeyID) FROM qiushibaike))+(SELECT MIN(KeyID) FROM qiushibaike)) AS KeyID) AS t2 
WHERE t1.KeyID >= t2.KeyID 
ORDER BY t1.KeyID LIMIT 5;";
            List<MQiushibaike> qiushibaikeList = new List<MQiushibaike>();
            MQiushibaike qiushibaike = null;
            using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sqlText, null))
            {
                while (reader.Read())
                {
                    qiushibaike = new MQiushibaike();
                    qiushibaike.KeyID = reader["KeyID"] == DBNull.Value ? string.Empty : reader["KeyID"].ToString();
                    qiushibaike.JokerName = reader["JokerName"] == DBNull.Value ? string.Empty : reader["JokerName"].ToString();
                    qiushibaike.JokeContent = reader["JokeContent"] == DBNull.Value ? string.Empty : reader["JokeContent"].ToString();
                    qiushibaike.Lauds = reader["Lauds"] == DBNull.Value ? 0 : Convert.ToInt32(reader["Lauds"]);
                    qiushibaikeList.Add(qiushibaike);
                }
            }

            return qiushibaikeList;
        }
    }
}
